
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;



with dm_prescription_reach_details_dt as (
    select
         island_code                  as order_source_code   --订单来源编码
        ,island_name                  as order_source_name   --订单来源名称
        ,start_pick_network_code      as start_network_code  --始发网点编码
        ,start_pick_network_name      as start_network_name  --始发网点名称
        ,start_parent_network_code    as start_fran_code     --始发加盟商code
        ,start_parent_network_name    as start_fran_name     --始发加盟商name
        ,start_city_id                as start_city_code     --始发城市id
        ,start_city_desc              as start_city_name     --始发城市name
        ,start_provider_id            as start_provider_code --始发省份id
        ,start_provider_desc          as start_provider_name --始发省份name
        ,start_subordinate_agent_code as start_agent_code    --始发代理区code
        ,start_subordinate_agent_name as start_agent_name    --始发代理区name
        ,start_center_code            as start_center_code   --始发转运中心code
        ,start_center_name            as start_center_name   --始发转运中心name
        ,sign_network_code            as end_network_code    --签收网点编码
        ,sign_network_name            as end_network_name    --签收网点名称
        ,sign_parent_network_code     as end_fran_code       --签收加盟商code
        ,sign_parent_network_name     as end_fran_name       --签收加盟商name
        ,sign_city_id                 as end_city_code       --签收城市id
        ,sign_city_desc               as end_city_name       --签收城市name
        ,sign_provider_id             as end_provider_code   --签收省份id
        ,sign_provider_desc           as end_provider_name   --签收省份name
        ,sign_subordinate_agent_code  as end_agent_code      --签收代理区code
        ,sign_subordinate_agent_name  as end_agent_name      --签收代理区name
        ,sign_center_code             as end_center_code     --目的转运中心code
        ,sign_center_name             as end_center_name     --目的转运中心name
        ,to_date(plan_sign_date)      as ziyou_plan_sign_date
        ,to_date(plan_cainiao_platform_date) as cainiao_plan_sign_date
        ,count(waybill_no) as  all_cnt --达成率总票数
        ,count(case when cainiao_is_platform_pres_standard = 1 then waybill_no end) as reach_cnt      --七星潭达成率达成总票数
        ,count(case when cainiao_is_platform_pres_standard = 0 then waybill_no end) as not_reach_cnt  --七星潭达成率达成总票数
        ,count(case when is_pres_standard = 1 then waybill_no end) as zy_reach_cnt      --自有平台达成率达成总票数
        ,count(case when is_pres_standard = 0 then waybill_no end) as zy_not_reach_cnt  --自有平台达成率达成总票数
    from jms_dm.dm_prescription_reach_details_dt reach --达成率明细
    where reach.dt between date_sub('{{ execution_date | cst_ds }}',26) and '{{ execution_date | cst_ds }}'
    and to_date(reach.plan_cainiao_platform_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by island_code
            ,island_name
            ,start_pick_network_code
            ,start_pick_network_name
            ,start_parent_network_code
            ,start_parent_network_name
            ,start_city_id
            ,start_city_desc
            ,start_provider_id
            ,start_provider_desc
            ,start_subordinate_agent_code
            ,start_subordinate_agent_name
            ,start_center_code
            ,start_center_name
            ,sign_network_code
            ,sign_network_name
            ,sign_parent_network_code
            ,sign_parent_network_name
            ,sign_city_id
            ,sign_city_desc
            ,sign_provider_id
            ,sign_provider_desc
            ,sign_subordinate_agent_code
            ,sign_subordinate_agent_name
            ,sign_center_code
            ,sign_center_name
            ,to_date(plan_sign_date)
            ,to_date(plan_cainiao_platform_date)
)


insert overwrite table jms_dm.dm_whole_decide_duty_flow_inport_true_dt
--代理区-代理区
select
     '代理区-代理区' as search_type
    ,'七星潭'        as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,null as start_provider_code
    ,null as start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,null as end_provider_code
    ,null as end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,main.jingtai_lyqs_cnt              as jingtai_lyqs_cnt          --七星潭静态路由缺失票数
    ,main.dizhi_jxcw_cnt                as dizhi_jxcw_cnt            --七星潭地址解析错误票数
    ,main.jingtai_my_cnt                as jingtai_my_cnt            --七星潭静态慢于票数
    ,main.jiedanhou_ls_cnt              as jiedanhou_ls_cnt          --七星潭截单后揽收票数
    ,main.jiaojian_jbcw_cnt             as jiaojian_jbcw_cnt         --七星潭交件建包错误票数
    ,main.cuojiao_cnt                   as cuojiao_cnt               --七星潭错交票数
    ,main.jiaojian_bzd_cnt              as jiaojian_bzd_cnt          --七星潭交件不准点票数
    ,main.zhongzhuan_jbcw_cnt           as zhongzhuan_jbcw_cnt       --七星潭中转建包错误票数
    ,main.cuofa_cnt                     as cuofa_cnt                 --七星潭错发票数
    ,main.fengche_wd_cnt                as fengche_wd_cnt            --七星潭封车晚点票数
    ,main.caozuo_yw_cnt                 as caozuo_yw_cnt             --七星潭操作延误票数
    ,main.fache_wd_cnt                  as fache_wd_cnt              --七星潭发车晚点票数
    ,main.yunshu_wd_cnt                 as yunshu_wd_cnt             --七星潭运输晚点票数
    ,main.jingang_zybjs_cnt             as jingang_zybjs_cnt         --七星潭进港转运不及时票数
    ,main.zhongzhuan_cf_cnt             as zhongzhuan_cf_cnt         --七星潭中转错分票数
    ,main.sanhuo_bjs_cnt                as sanhuo_bjs_cnt            --七星潭散货不及时票数
    ,main.paijian_cf_cnt                as paijian_cf_cnt            --七星潭派件错分票数
    ,main.qianshou_wd_cnt               as qianshou_wd_cnt           --七星潭签收晚点票数
    ,main.tuizhuan_cnt                  as tuizhuan_cnt              --七星潭退转件票数
    ,main.duty_cnt                      as duty_cnt                  --七星潭判责总票数(未达成票数)
    ,null                               as zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,null                               as zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,null                               as zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,null                               as zy_cuojiao_cnt            --自有平台错交票数
    ,null                               as zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,null                               as zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,null                               as zy_cuofa_cnt              --自有平台错发票数
    ,null                               as zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,null                               as zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,null                               as zy_fache_wd_cnt           --自有平台发车晚点票数
    ,null                               as zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,null                               as zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,null                               as zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,null                               as zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,null                               as zy_paijian_cf_cnt         --自有平台派件错分票数
    ,null                               as zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,null                               as zy_tuizhuan_cnt           --自有平台退转件票数
    ,null                               as zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                      as all_cnt                   --达成率总票数
    ,reach.reach_cnt                    as reach_cnt                 --七星潭达成率达成总票数
    ,reach.not_reach_cnt                as not_reach_cnt             --七星潭达成率未达成总票数
    ,null                               as zy_reach_cnt              --达成率达成总票数
    ,null                               as zy_not_reach_cnt          --达成率未达成总票数
    ,reach.cainiao_plan_sign_date        as cainiao_plan_sign_date    --菜鸟平台规划签收日期
    ,null                               as ziyou_plan_sign_date      --自有平台规划签收日期
    ,main.jingtai_my_dbc_cnt            as jingtai_my_dbc_cnt        --七星潭静态慢于(多班次)票数
    ,main.xiangzhen_js_cnt              as xiangzhen_js_cnt          --七星潭乡镇加时票数
    ,main.zhongzhuan_lsdd_cnt           as zhongzhuan_lsdd_cnt       --七星潭临时调度票数
    ,null                               as zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,main.beiyongly_cnt                 as beiyongly_cnt             --七星潭备用路由票数
    ,main.zhuanyou_cnt                  as zhuanyou_cnt              --七星潭转邮件票数
    ,null                               as zy_beiyongly_cnt          --自有平台备用路由票数
    ,null                               as zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.cainiao_plan_sign_date as dt
from (
    select
         start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(reach_cnt    ) as  reach_cnt      --七星潭达成率达成总票数
        ,sum(not_reach_cnt) as  not_reach_cnt  --七星潭达成率未达成总票数
    from dm_prescription_reach_details_dt
    where cainiao_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(jingtai_lyqs_cnt      ) as jingtai_lyqs_cnt         --七星潭静态路由缺失票数
        ,sum(dizhi_jxcw_cnt        ) as dizhi_jxcw_cnt           --七星潭地址解析错误票数
        ,sum(jingtai_my_cnt        ) as jingtai_my_cnt           --七星潭静态慢于票数
        ,sum(jiedanhou_ls_cnt      ) as jiedanhou_ls_cnt         --七星潭截单后揽收票数
        ,sum(jiaojian_jbcw_cnt     ) as jiaojian_jbcw_cnt        --七星潭交件建包错误票数
        ,sum(cuojiao_cnt           ) as cuojiao_cnt              --七星潭错交票数
        ,sum(jiaojian_bzd_cnt      ) as jiaojian_bzd_cnt         --七星潭交件不准点票数
        ,sum(zhongzhuan_jbcw_cnt   ) as zhongzhuan_jbcw_cnt      --七星潭中转建包错误票数
        ,sum(cuofa_cnt             ) as cuofa_cnt                --七星潭错发票数
        ,sum(fengche_wd_cnt        ) as fengche_wd_cnt           --七星潭封车晚点票数
        ,sum(caozuo_yw_cnt         ) as caozuo_yw_cnt            --七星潭操作延误票数
        ,sum(fache_wd_cnt          ) as fache_wd_cnt             --七星潭发车晚点票数
        ,sum(yunshu_wd_cnt         ) as yunshu_wd_cnt            --七星潭运输晚点票数
        ,sum(jingang_zybjs_cnt     ) as jingang_zybjs_cnt        --七星潭进港转运不及时票数
        ,sum(zhongzhuan_cf_cnt     ) as zhongzhuan_cf_cnt        --七星潭中转错分票数
        ,sum(sanhuo_bjs_cnt        ) as sanhuo_bjs_cnt           --七星潭散货不及时票数
        ,sum(paijian_cf_cnt        ) as paijian_cf_cnt           --七星潭派件错分票数
        ,sum(qianshou_wd_cnt       ) as qianshou_wd_cnt          --七星潭签收晚点票数
        ,sum(tuizhuan_cnt          ) as tuizhuan_cnt             --七星潭退转件票数
        ,sum(duty_cnt              ) as duty_cnt                 --七星潭判责总票数(未达成票数)
        ,sum(jingtai_my_dbc_cnt    ) as jingtai_my_dbc_cnt       --七星潭静态慢于（多班次）'
        ,sum(xiangzhen_js_cnt      ) as xiangzhen_js_cnt         --七星潭乡镇加时票数'
        ,sum(zhongzhuan_lsdd_cnt   ) as zhongzhuan_lsdd_cnt      --七星潭临时调度调度票数'
        ,sum(beiyongly_cnt         ) as beiyongly_cnt            --七星潭备用路由票数
        ,sum(zhuanyou_cnt          ) as zhuanyou_cnt             --七星潭转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_agent_code = duty_main_agent_code)
    and duty_type is not null
    group by order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) main on main.order_source_code = reach.order_source_code
      and main.order_source_name = reach.order_source_name
      and main.start_agent_code  = reach.start_agent_code
      and main.start_agent_name  = reach.start_agent_name
      and main.end_agent_code    = reach.end_agent_code
      and main.end_agent_name    = reach.end_agent_name
      and main.cainiao_plan_sign_date = reach.cainiao_plan_sign_date --菜鸟平台规划签收日期

union all
--网点-网点
select
     '网点-网点'                    as search_type
    ,'七星潭'                       as effect_type
    ,reach.order_source_code        as order_source_code
    ,reach.order_source_name        as order_source_name
    ,reach.start_network_code       as start_network_code
    ,reach.start_network_name       as start_network_name
    ,reach.start_city_code          as start_city_code
    ,reach.start_city_name          as start_city_name
    ,reach.start_provider_code      as start_provider_code
    ,reach.start_provider_name      as start_provider_name
    ,reach.start_fran_code          as start_fran_code
    ,reach.start_fran_name          as start_fran_name
    ,reach.start_agent_code         as start_agent_code
    ,reach.start_agent_name         as start_agent_name
    ,null                           as start_center_code
    ,null                           as start_center_name
    ,reach.end_network_code         as end_network_code
    ,reach.end_network_name         as end_network_name
    ,reach.end_city_code            as end_city_code
    ,reach.end_city_name            as end_city_name
    ,reach.end_provider_code        as end_provider_code
    ,reach.end_provider_name        as end_provider_name
    ,reach.end_fran_code            as end_fran_code
    ,reach.end_fran_name            as end_fran_name
    ,reach.end_agent_code           as end_agent_code
    ,reach.end_agent_name           as end_agent_name
    ,null                           as end_center_code
    ,null                           as end_center_name
    ,main.jingtai_lyqs_cnt          as jingtai_lyqs_cnt          --七星潭静态路由缺失票数
    ,main.dizhi_jxcw_cnt            as dizhi_jxcw_cnt            --七星潭地址解析错误票数
    ,main.jingtai_my_cnt            as jingtai_my_cnt            --七星潭静态慢于票数
    ,main.jiedanhou_ls_cnt          as jiedanhou_ls_cnt          --七星潭截单后揽收票数
    ,main.jiaojian_jbcw_cnt         as jiaojian_jbcw_cnt         --七星潭交件建包错误票数
    ,main.cuojiao_cnt               as cuojiao_cnt               --七星潭错交票数
    ,main.jiaojian_bzd_cnt          as jiaojian_bzd_cnt          --七星潭交件不准点票数
    ,main.zhongzhuan_jbcw_cnt       as zhongzhuan_jbcw_cnt       --七星潭中转建包错误票数
    ,main.cuofa_cnt                 as cuofa_cnt                 --七星潭错发票数
    ,main.fengche_wd_cnt            as fengche_wd_cnt            --七星潭封车晚点票数
    ,main.caozuo_yw_cnt             as caozuo_yw_cnt             --七星潭操作延误票数
    ,main.fache_wd_cnt              as fache_wd_cnt              --七星潭发车晚点票数
    ,main.yunshu_wd_cnt             as yunshu_wd_cnt             --七星潭运输晚点票数
    ,main.jingang_zybjs_cnt         as jingang_zybjs_cnt         --七星潭进港转运不及时票数
    ,main.zhongzhuan_cf_cnt         as zhongzhuan_cf_cnt         --七星潭中转错分票数
    ,main.sanhuo_bjs_cnt            as sanhuo_bjs_cnt            --七星潭散货不及时票数
    ,main.paijian_cf_cnt            as paijian_cf_cnt            --七星潭派件错分票数
    ,main.qianshou_wd_cnt           as qianshou_wd_cnt           --七星潭签收晚点票数
    ,main.tuizhuan_cnt              as tuizhuan_cnt              --七星潭退转件票数
    ,main.duty_cnt                  as duty_cnt                  --七星潭判责总票数(未达成票数)
    ,null                           as zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,null                           as zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,null                           as zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,null                           as zy_cuojiao_cnt            --自有平台错交票数
    ,null                           as zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,null                           as zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,null                           as zy_cuofa_cnt              --自有平台错发票数
    ,null                           as zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,null                           as zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,null                           as zy_fache_wd_cnt           --自有平台发车晚点票数
    ,null                           as zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,null                           as zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,null                           as zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,null                           as zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,null                           as zy_paijian_cf_cnt         --自有平台派件错分票数
    ,null                           as zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,null                           as zy_tuizhuan_cnt           --自有平台退转件票数
    ,null                           as zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  as all_cnt                   --达成率总票数
    ,reach.reach_cnt                as reach_cnt                 --七星潭达成率达成总票数
    ,reach.not_reach_cnt            as not_reach_cnt             --七星潭达成率未达成总票数
    ,null                           as zy_reach_cnt              --达成率达成总票数
    ,null                           as zy_not_reach_cnt          --达成率未达成总票数
    ,reach.cainiao_plan_sign_date   as cainiao_plan_sign_date    --菜鸟平台规划签收日期
    ,null                           as ziyou_plan_sign_date      --自有平台规划签收日期
    ,main.jingtai_my_dbc_cnt        as jingtai_my_dbc_cnt        --七星潭静态慢于(多班次)票数
    ,main.xiangzhen_js_cnt          as xiangzhen_js_cnt          --七星潭乡镇加时票数
    ,main.zhongzhuan_lsdd_cnt       as zhongzhuan_lsdd_cnt       --七星潭临时调度票数
    ,null                           as zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,main.beiyongly_cnt             as beiyongly_cnt             --七星潭备用路由票数
    ,main.zhuanyou_cnt              as zhuanyou_cnt              --七星潭转邮件票数
    ,null                           as zy_beiyongly_cnt          --自有平台备用路由票数
    ,null                           as zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.cainiao_plan_sign_date as dt
from (
    select
         start_network_code
        ,start_network_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_fran_code
        ,start_fran_name
        ,start_agent_code
        ,start_agent_name
        ,end_network_code
        ,end_network_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_fran_code
        ,end_fran_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(reach_cnt        ) as  reach_cnt      --七星潭达成率达成总票数
        ,sum(not_reach_cnt    ) as  not_reach_cnt  --七星潭达成率未达成总票数
    from dm_prescription_reach_details_dt
    where cainiao_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_network_code
            ,start_network_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_fran_code
            ,start_fran_name
            ,start_agent_code
            ,start_agent_name
            ,end_network_code
            ,end_network_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_fran_code
            ,end_fran_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_network_code
        ,start_network_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_fran_code
        ,start_fran_name
        ,start_agent_code
        ,start_agent_name
        ,end_network_code
        ,end_network_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_fran_code
        ,end_fran_name
        ,end_agent_code
        ,end_agent_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(jingtai_lyqs_cnt      ) as jingtai_lyqs_cnt         --七星潭静态路由缺失票数
        ,sum(dizhi_jxcw_cnt        ) as dizhi_jxcw_cnt           --七星潭地址解析错误票数
        ,sum(jingtai_my_cnt        ) as jingtai_my_cnt           --七星潭静态慢于票数
        ,sum(jiedanhou_ls_cnt      ) as jiedanhou_ls_cnt         --七星潭截单后揽收票数
        ,sum(jiaojian_jbcw_cnt     ) as jiaojian_jbcw_cnt        --七星潭交件建包错误票数
        ,sum(cuojiao_cnt           ) as cuojiao_cnt              --七星潭错交票数
        ,sum(jiaojian_bzd_cnt      ) as jiaojian_bzd_cnt         --七星潭交件不准点票数
        ,sum(zhongzhuan_jbcw_cnt   ) as zhongzhuan_jbcw_cnt      --七星潭中转建包错误票数
        ,sum(cuofa_cnt             ) as cuofa_cnt                --七星潭错发票数
        ,sum(fengche_wd_cnt        ) as fengche_wd_cnt           --七星潭封车晚点票数
        ,sum(caozuo_yw_cnt         ) as caozuo_yw_cnt            --七星潭操作延误票数
        ,sum(fache_wd_cnt          ) as fache_wd_cnt             --七星潭发车晚点票数
        ,sum(yunshu_wd_cnt         ) as yunshu_wd_cnt            --七星潭运输晚点票数
        ,sum(jingang_zybjs_cnt     ) as jingang_zybjs_cnt        --七星潭进港转运不及时票数
        ,sum(zhongzhuan_cf_cnt     ) as zhongzhuan_cf_cnt        --七星潭中转错分票数
        ,sum(sanhuo_bjs_cnt        ) as sanhuo_bjs_cnt           --七星潭散货不及时票数
        ,sum(paijian_cf_cnt        ) as paijian_cf_cnt           --七星潭派件错分票数
        ,sum(qianshou_wd_cnt       ) as qianshou_wd_cnt          --七星潭签收晚点票数
        ,sum(tuizhuan_cnt          ) as tuizhuan_cnt             --七星潭退转件票数
        ,sum(duty_cnt              ) as duty_cnt                 --七星潭判责总票数(未达成票数)
        ,sum(jingtai_my_dbc_cnt    ) as jingtai_my_dbc_cnt       --七星潭静态慢于（多班次）'
        ,sum(xiangzhen_js_cnt      ) as xiangzhen_js_cnt         --七星潭乡镇加时票数'
        ,sum(zhongzhuan_lsdd_cnt   ) as zhongzhuan_lsdd_cnt      --七星潭临时调度调度票数'
        ,sum(beiyongly_cnt         ) as beiyongly_cnt            --七星潭备用路由票数
        ,sum(zhuanyou_cnt          ) as zhuanyou_cnt             --七星潭转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_network_code = duty_main_code)
    and duty_type is not null
    group by order_source_code
            ,order_source_name
            ,start_network_code
            ,start_network_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_fran_code
            ,start_fran_name
            ,start_agent_code
            ,start_agent_name
            ,end_network_code
            ,end_network_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_fran_code
            ,end_fran_name
            ,end_agent_code
            ,end_agent_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_network_code  = reach.start_network_code
      and main.start_network_name  = reach.start_network_name
      and main.start_city_code     = reach.start_city_code
      and main.start_city_name     = reach.start_city_name
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.start_fran_code     = reach.start_fran_code
      and main.start_fran_name     = reach.start_fran_name
      and main.start_agent_code    = reach.start_agent_code
      and main.start_agent_name    = reach.start_agent_name
      and main.end_network_code    = reach.end_network_code
      and main.end_network_name    = reach.end_network_name
      and main.end_city_code       = reach.end_city_code
      and main.end_city_name       = reach.end_city_name
      and main.end_provider_code   = reach.end_provider_code
      and main.end_provider_name   = reach.end_provider_name
      and main.end_fran_code       = reach.end_fran_code
      and main.end_fran_name       = reach.end_fran_name
      and main.end_agent_code      = reach.end_agent_code
      and main.end_agent_name      = reach.end_agent_name
      and main.cainiao_plan_sign_date = reach.cainiao_plan_sign_date --菜鸟平台规划签收日期


union all
--中心-中心
select
     '中心-中心' as search_type
    ,'七星潭'    as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,null as start_provider_code
    ,null as start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,reach.start_center_code
    ,reach.start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,null as end_provider_code
    ,null as end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,reach.end_center_code
    ,reach.end_center_name
    ,main.jingtai_lyqs_cnt          --七星潭静态路由缺失票数
    ,main.dizhi_jxcw_cnt            --七星潭地址解析错误票数
    ,main.jingtai_my_cnt            --七星潭静态慢于票数
    ,main.jiedanhou_ls_cnt          --七星潭截单后揽收票数
    ,main.jiaojian_jbcw_cnt         --七星潭交件建包错误票数
    ,main.cuojiao_cnt               --七星潭错交票数
    ,main.jiaojian_bzd_cnt          --七星潭交件不准点票数
    ,main.zhongzhuan_jbcw_cnt       --七星潭中转建包错误票数
    ,main.cuofa_cnt                 --七星潭错发票数
    ,main.fengche_wd_cnt            --七星潭封车晚点票数
    ,main.caozuo_yw_cnt             --七星潭操作延误票数
    ,main.fache_wd_cnt              --七星潭发车晚点票数
    ,main.yunshu_wd_cnt             --七星潭运输晚点票数
    ,main.jingang_zybjs_cnt         --七星潭进港转运不及时票数
    ,main.zhongzhuan_cf_cnt         --七星潭中转错分票数
    ,main.sanhuo_bjs_cnt            --七星潭散货不及时票数
    ,main.paijian_cf_cnt            --七星潭派件错分票数
    ,main.qianshou_wd_cnt           --七星潭签收晚点票数
    ,main.tuizhuan_cnt              --七星潭退转件票数
    ,main.duty_cnt                  --七星潭判责总票数(未达成票数)
    ,null                           as zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,null                           as zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,null                           as zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,null                           as zy_cuojiao_cnt            --自有平台错交票数
    ,null                           as zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,null                           as zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,null                           as zy_cuofa_cnt              --自有平台错发票数
    ,null                           as zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,null                           as zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,null                           as zy_fache_wd_cnt           --自有平台发车晚点票数
    ,null                           as zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,null                           as zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,null                           as zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,null                           as zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,null                           as zy_paijian_cf_cnt         --自有平台派件错分票数
    ,null                           as zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,null                           as zy_tuizhuan_cnt           --自有平台退转件票数
    ,null                           as zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  as all_cnt                   --达成率总票数
    ,reach.reach_cnt                as reach_cnt                 --七星潭达成率达成总票数
    ,reach.not_reach_cnt            as not_reach_cnt             --七星潭达成率未达成总票数
    ,null                           as zy_reach_cnt              --达成率达成总票数
    ,null                           as zy_not_reach_cnt          --达成率未达成总票数
    ,reach.cainiao_plan_sign_date   as cainiao_plan_sign_date    --菜鸟平台规划签收日期
    ,null                           as ziyou_plan_sign_date      --自有平台规划签收日期
    ,main.jingtai_my_dbc_cnt        as jingtai_my_dbc_cnt        --七星潭静态慢于(多班次)票数
    ,main.xiangzhen_js_cnt          as xiangzhen_js_cnt          --七星潭乡镇加时票数
    ,main.zhongzhuan_lsdd_cnt       as zhongzhuan_lsdd_cnt       --七星潭临时调度票数
    ,null                           as zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,main.beiyongly_cnt             as beiyongly_cnt             --七星潭备用路由票数
    ,main.zhuanyou_cnt              as zhuanyou_cnt              --七星潭转邮件票数
    ,null                           as zy_beiyongly_cnt          --自有平台备用路由票数
    ,null                           as zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.cainiao_plan_sign_date   as dt
from (
    select
         start_agent_code
        ,start_agent_name
        ,start_center_code
        ,start_center_name
        ,end_agent_code
        ,end_agent_name
        ,end_center_code
        ,end_center_name
        ,order_source_code
        ,order_source_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(reach_cnt        ) as  reach_cnt      --七星潭达成率达成总票数
        ,sum(not_reach_cnt    ) as  not_reach_cnt  --七星潭达成率未达成总票数
    from dm_prescription_reach_details_dt
    where cainiao_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_agent_code
            ,start_agent_name
            ,start_center_code
            ,start_center_name
            ,end_agent_code
            ,end_agent_name
            ,end_center_code
            ,end_center_name
            ,order_source_code
            ,order_source_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,start_center_code
        ,start_center_name
        ,end_agent_code
        ,end_agent_name
        ,end_center_code
        ,end_center_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(jingtai_lyqs_cnt      ) as jingtai_lyqs_cnt         --七星潭静态路由缺失票数
        ,sum(dizhi_jxcw_cnt        ) as dizhi_jxcw_cnt           --七星潭地址解析错误票数
        ,sum(jingtai_my_cnt        ) as jingtai_my_cnt           --七星潭静态慢于票数
        ,sum(jiedanhou_ls_cnt      ) as jiedanhou_ls_cnt         --七星潭截单后揽收票数
        ,sum(jiaojian_jbcw_cnt     ) as jiaojian_jbcw_cnt        --七星潭交件建包错误票数
        ,sum(cuojiao_cnt           ) as cuojiao_cnt              --七星潭错交票数
        ,sum(jiaojian_bzd_cnt      ) as jiaojian_bzd_cnt         --七星潭交件不准点票数
        ,sum(zhongzhuan_jbcw_cnt   ) as zhongzhuan_jbcw_cnt      --七星潭中转建包错误票数
        ,sum(cuofa_cnt             ) as cuofa_cnt                --七星潭错发票数
        ,sum(fengche_wd_cnt        ) as fengche_wd_cnt           --七星潭封车晚点票数
        ,sum(caozuo_yw_cnt         ) as caozuo_yw_cnt            --七星潭操作延误票数
        ,sum(fache_wd_cnt          ) as fache_wd_cnt             --七星潭发车晚点票数
        ,sum(yunshu_wd_cnt         ) as yunshu_wd_cnt            --七星潭运输晚点票数
        ,sum(jingang_zybjs_cnt     ) as jingang_zybjs_cnt        --七星潭进港转运不及时票数
        ,sum(zhongzhuan_cf_cnt     ) as zhongzhuan_cf_cnt        --七星潭中转错分票数
        ,sum(sanhuo_bjs_cnt        ) as sanhuo_bjs_cnt           --七星潭散货不及时票数
        ,sum(paijian_cf_cnt        ) as paijian_cf_cnt           --七星潭派件错分票数
        ,sum(qianshou_wd_cnt       ) as qianshou_wd_cnt          --七星潭签收晚点票数
        ,sum(tuizhuan_cnt          ) as tuizhuan_cnt             --七星潭退转件票数
        ,sum(duty_cnt              ) as duty_cnt                 --七星潭判责总票数(未达成票数)
        ,sum(jingtai_my_dbc_cnt    ) as jingtai_my_dbc_cnt       --七星潭静态慢于（多班次）'
        ,sum(xiangzhen_js_cnt      ) as xiangzhen_js_cnt         --七星潭乡镇加时票数'
        ,sum(zhongzhuan_lsdd_cnt   ) as zhongzhuan_lsdd_cnt      --七星潭临时调度调度票数'
        ,sum(beiyongly_cnt         ) as beiyongly_cnt            --七星潭备用路由票数
        ,sum(zhuanyou_cnt          ) as zhuanyou_cnt             --七星潭转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_center_code = duty_main_code)
    and duty_type is not null
    group by order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,start_center_code
            ,start_center_name
            ,end_agent_code
            ,end_agent_name
            ,end_center_code
            ,end_center_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_agent_code    = reach.start_agent_code
      and main.start_agent_name    = reach.start_agent_name
      and main.start_center_code   = reach.start_center_code
      and main.start_center_name   = reach.start_center_name
      and main.end_agent_code      = reach.end_agent_code
      and main.end_agent_name      = reach.end_agent_name
      and main.end_center_code     = reach.end_center_code
      and main.end_center_name     = reach.end_center_name
      and main.cainiao_plan_sign_date = reach.cainiao_plan_sign_date --菜鸟平台规划签收日期


union all
--省份-省份
select
     '省份-省份' as search_type
    ,'七星潭'    as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,reach.start_provider_code
    ,reach.start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,reach.end_provider_code
    ,reach.end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,main.jingtai_lyqs_cnt          --七星潭静态路由缺失票数
    ,main.dizhi_jxcw_cnt            --七星潭地址解析错误票数
    ,main.jingtai_my_cnt            --七星潭静态慢于票数
    ,main.jiedanhou_ls_cnt          --七星潭截单后揽收票数
    ,main.jiaojian_jbcw_cnt         --七星潭交件建包错误票数
    ,main.cuojiao_cnt               --七星潭错交票数
    ,main.jiaojian_bzd_cnt          --七星潭交件不准点票数
    ,main.zhongzhuan_jbcw_cnt       --七星潭中转建包错误票数
    ,main.cuofa_cnt                 --七星潭错发票数
    ,main.fengche_wd_cnt            --七星潭封车晚点票数
    ,main.caozuo_yw_cnt             --七星潭操作延误票数
    ,main.fache_wd_cnt              --七星潭发车晚点票数
    ,main.yunshu_wd_cnt             --七星潭运输晚点票数
    ,main.jingang_zybjs_cnt         --七星潭进港转运不及时票数
    ,main.zhongzhuan_cf_cnt         --七星潭中转错分票数
    ,main.sanhuo_bjs_cnt            --七星潭散货不及时票数
    ,main.paijian_cf_cnt            --七星潭派件错分票数
    ,main.qianshou_wd_cnt           --七星潭签收晚点票数
    ,main.tuizhuan_cnt              --七星潭退转件票数
    ,main.duty_cnt                  --七星潭判责总票数(未达成票数)
    ,null                           as zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,null                           as zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,null                           as zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,null                           as zy_cuojiao_cnt            --自有平台错交票数
    ,null                           as zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,null                           as zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,null                           as zy_cuofa_cnt              --自有平台错发票数
    ,null                           as zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,null                           as zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,null                           as zy_fache_wd_cnt           --自有平台发车晚点票数
    ,null                           as zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,null                           as zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,null                           as zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,null                           as zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,null                           as zy_paijian_cf_cnt         --自有平台派件错分票数
    ,null                           as zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,null                           as zy_tuizhuan_cnt           --自有平台退转件票数
    ,null                           as zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  as all_cnt                   --达成率总票数
    ,reach.reach_cnt                as reach_cnt                 --七星潭达成率达成总票数
    ,reach.not_reach_cnt            as not_reach_cnt             --七星潭达成率未达成总票数
    ,null                           as zy_reach_cnt              --达成率达成总票数
    ,null                           as zy_not_reach_cnt          --达成率未达成总票数
    ,reach.cainiao_plan_sign_date   as cainiao_plan_sign_date    --菜鸟平台规划签收日期
    ,null                           as ziyou_plan_sign_date      --自有平台规划签收日期
    ,main.jingtai_my_dbc_cnt        as jingtai_my_dbc_cnt        --七星潭静态慢于(多班次)票数
    ,main.xiangzhen_js_cnt          as xiangzhen_js_cnt          --七星潭乡镇加时票数
    ,main.zhongzhuan_lsdd_cnt       as zhongzhuan_lsdd_cnt       --七星潭临时调度票数
    ,null                           as zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,main.beiyongly_cnt             as beiyongly_cnt             --七星潭备用路由票数
    ,main.zhuanyou_cnt              as zhuanyou_cnt              --七星潭转邮件票数
    ,null                           as zy_beiyongly_cnt          --自有平台备用路由票数
    ,null                           as zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.cainiao_plan_sign_date   as dt
from (
    select
         start_provider_code
        ,start_provider_name
        ,end_provider_code
        ,end_provider_name
        ,order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(reach_cnt        ) as  reach_cnt          --七星潭达成率达成总票数
        ,sum(not_reach_cnt    ) as  not_reach_cnt      --七星潭达成率未达成总票数
    from dm_prescription_reach_details_dt
    where cainiao_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_provider_code
            ,start_provider_name
            ,end_provider_code
            ,end_provider_name
            ,order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_provider_code
        ,start_provider_name
        ,end_provider_code
        ,end_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(jingtai_lyqs_cnt      ) as jingtai_lyqs_cnt         --七星潭静态路由缺失票数
        ,sum(dizhi_jxcw_cnt        ) as dizhi_jxcw_cnt           --七星潭地址解析错误票数
        ,sum(jingtai_my_cnt        ) as jingtai_my_cnt           --七星潭静态慢于票数
        ,sum(jiedanhou_ls_cnt      ) as jiedanhou_ls_cnt         --七星潭截单后揽收票数
        ,sum(jiaojian_jbcw_cnt     ) as jiaojian_jbcw_cnt        --七星潭交件建包错误票数
        ,sum(cuojiao_cnt           ) as cuojiao_cnt              --七星潭错交票数
        ,sum(jiaojian_bzd_cnt      ) as jiaojian_bzd_cnt         --七星潭交件不准点票数
        ,sum(zhongzhuan_jbcw_cnt   ) as zhongzhuan_jbcw_cnt      --七星潭中转建包错误票数
        ,sum(cuofa_cnt             ) as cuofa_cnt                --七星潭错发票数
        ,sum(fengche_wd_cnt        ) as fengche_wd_cnt           --七星潭封车晚点票数
        ,sum(caozuo_yw_cnt         ) as caozuo_yw_cnt            --七星潭操作延误票数
        ,sum(fache_wd_cnt          ) as fache_wd_cnt             --七星潭发车晚点票数
        ,sum(yunshu_wd_cnt         ) as yunshu_wd_cnt            --七星潭运输晚点票数
        ,sum(jingang_zybjs_cnt     ) as jingang_zybjs_cnt        --七星潭进港转运不及时票数
        ,sum(zhongzhuan_cf_cnt     ) as zhongzhuan_cf_cnt        --七星潭中转错分票数
        ,sum(sanhuo_bjs_cnt        ) as sanhuo_bjs_cnt           --七星潭散货不及时票数
        ,sum(paijian_cf_cnt        ) as paijian_cf_cnt           --七星潭派件错分票数
        ,sum(qianshou_wd_cnt       ) as qianshou_wd_cnt          --七星潭签收晚点票数
        ,sum(tuizhuan_cnt          ) as tuizhuan_cnt             --七星潭退转件票数
        ,sum(duty_cnt              ) as duty_cnt                 --七星潭判责总票数(未达成票数)
        ,sum(jingtai_my_dbc_cnt    ) as jingtai_my_dbc_cnt       --七星潭静态慢于（多班次）'
        ,sum(xiangzhen_js_cnt      ) as xiangzhen_js_cnt         --七星潭乡镇加时票数'
        ,sum(zhongzhuan_lsdd_cnt   ) as zhongzhuan_lsdd_cnt      --七星潭临时调度调度票数'
        ,sum(beiyongly_cnt         ) as beiyongly_cnt            --七星潭备用路由票数
        ,sum(zhuanyou_cnt          ) as zhuanyou_cnt             --七星潭转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_provider_code = duty_provider_code)
    and duty_type is not null
    group by order_source_code
            ,order_source_name
            ,start_provider_code
            ,start_provider_name
            ,end_provider_code
            ,end_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.end_provider_code   = reach.end_provider_code
      and main.end_provider_name   = reach.end_provider_name
      and main.start_agent_code = reach.start_agent_code
      and main.start_agent_name = reach.start_agent_name
      and main.end_agent_code   = reach.end_agent_code
      and main.end_agent_name   = reach.end_agent_name
      and main.cainiao_plan_sign_date = reach.cainiao_plan_sign_date --菜鸟平台规划签收日期

union all
--城市-城市
select
     '城市-城市' as search_type
    ,'七星潭'    as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,reach.start_city_code
    ,reach.start_city_name
    ,reach.start_provider_code
    ,reach.start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,reach.end_city_code
    ,reach.end_city_name
    ,reach.end_provider_code
    ,reach.end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,main.jingtai_lyqs_cnt          --七星潭静态路由缺失票数
    ,main.dizhi_jxcw_cnt            --七星潭地址解析错误票数
    ,main.jingtai_my_cnt            --七星潭静态慢于票数
    ,main.jiedanhou_ls_cnt          --七星潭截单后揽收票数
    ,main.jiaojian_jbcw_cnt         --七星潭交件建包错误票数
    ,main.cuojiao_cnt               --七星潭错交票数
    ,main.jiaojian_bzd_cnt          --七星潭交件不准点票数
    ,main.zhongzhuan_jbcw_cnt       --七星潭中转建包错误票数
    ,main.cuofa_cnt                 --七星潭错发票数
    ,main.fengche_wd_cnt            --七星潭封车晚点票数
    ,main.caozuo_yw_cnt             --七星潭操作延误票数
    ,main.fache_wd_cnt              --七星潭发车晚点票数
    ,main.yunshu_wd_cnt             --七星潭运输晚点票数
    ,main.jingang_zybjs_cnt         --七星潭进港转运不及时票数
    ,main.zhongzhuan_cf_cnt         --七星潭中转错分票数
    ,main.sanhuo_bjs_cnt            --七星潭散货不及时票数
    ,main.paijian_cf_cnt            --七星潭派件错分票数
    ,main.qianshou_wd_cnt           --七星潭签收晚点票数
    ,main.tuizhuan_cnt              --七星潭退转件票数
    ,main.duty_cnt                  --七星潭判责总票数(未达成票数)
    ,null                           as zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,null                           as zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,null                           as zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,null                           as zy_cuojiao_cnt            --自有平台错交票数
    ,null                           as zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,null                           as zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,null                           as zy_cuofa_cnt              --自有平台错发票数
    ,null                           as zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,null                           as zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,null                           as zy_fache_wd_cnt           --自有平台发车晚点票数
    ,null                           as zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,null                           as zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,null                           as zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,null                           as zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,null                           as zy_paijian_cf_cnt         --自有平台派件错分票数
    ,null                           as zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,null                           as zy_tuizhuan_cnt           --自有平台退转件票数
    ,null                           as zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  as all_cnt                   --达成率总票数
    ,reach.reach_cnt                as reach_cnt                 --七星潭达成率达成总票数
    ,reach.not_reach_cnt            as not_reach_cnt             --七星潭达成率未达成总票数
    ,null                           as zy_reach_cnt              --达成率达成总票数
    ,null                           as zy_not_reach_cnt          --达成率未达成总票数
    ,reach.cainiao_plan_sign_date   as cainiao_plan_sign_date    --菜鸟平台规划签收日期
    ,null                           as ziyou_plan_sign_date      --自有平台规划签收日期
    ,main.jingtai_my_dbc_cnt        as jingtai_my_dbc_cnt        --七星潭静态慢于(多班次)票数
    ,main.xiangzhen_js_cnt          as xiangzhen_js_cnt          --七星潭乡镇加时票数
    ,main.zhongzhuan_lsdd_cnt       as zhongzhuan_lsdd_cnt       --七星潭临时调度票数
    ,null                           as zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,main.beiyongly_cnt             as beiyongly_cnt             --七星潭备用路由票数
    ,main.zhuanyou_cnt              as zhuanyou_cnt              --七星潭转邮件票数
    ,null                           as zy_beiyongly_cnt          --自有平台备用路由票数
    ,null                           as zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.cainiao_plan_sign_date   as dt
from (
    select
         start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(reach_cnt        ) as  reach_cnt      --七星潭达成率达成总票数
        ,sum(not_reach_cnt    ) as  not_reach_cnt  --七星潭达成率未达成总票数
    from dm_prescription_reach_details_dt
    where cainiao_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_agent_code
        ,end_agent_name
        ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
        ,sum(jingtai_lyqs_cnt      ) as jingtai_lyqs_cnt         --七星潭静态路由缺失票数
        ,sum(dizhi_jxcw_cnt        ) as dizhi_jxcw_cnt           --七星潭地址解析错误票数
        ,sum(jingtai_my_cnt        ) as jingtai_my_cnt           --七星潭静态慢于票数
        ,sum(jiedanhou_ls_cnt      ) as jiedanhou_ls_cnt         --七星潭截单后揽收票数
        ,sum(jiaojian_jbcw_cnt     ) as jiaojian_jbcw_cnt        --七星潭交件建包错误票数
        ,sum(cuojiao_cnt           ) as cuojiao_cnt              --七星潭错交票数
        ,sum(jiaojian_bzd_cnt      ) as jiaojian_bzd_cnt         --七星潭交件不准点票数
        ,sum(zhongzhuan_jbcw_cnt   ) as zhongzhuan_jbcw_cnt      --七星潭中转建包错误票数
        ,sum(cuofa_cnt             ) as cuofa_cnt                --七星潭错发票数
        ,sum(fengche_wd_cnt        ) as fengche_wd_cnt           --七星潭封车晚点票数
        ,sum(caozuo_yw_cnt         ) as caozuo_yw_cnt            --七星潭操作延误票数
        ,sum(fache_wd_cnt          ) as fache_wd_cnt             --七星潭发车晚点票数
        ,sum(yunshu_wd_cnt         ) as yunshu_wd_cnt            --七星潭运输晚点票数
        ,sum(jingang_zybjs_cnt     ) as jingang_zybjs_cnt        --七星潭进港转运不及时票数
        ,sum(zhongzhuan_cf_cnt     ) as zhongzhuan_cf_cnt        --七星潭中转错分票数
        ,sum(sanhuo_bjs_cnt        ) as sanhuo_bjs_cnt           --七星潭散货不及时票数
        ,sum(paijian_cf_cnt        ) as paijian_cf_cnt           --七星潭派件错分票数
        ,sum(qianshou_wd_cnt       ) as qianshou_wd_cnt          --七星潭签收晚点票数
        ,sum(tuizhuan_cnt          ) as tuizhuan_cnt             --七星潭退转件票数
        ,sum(duty_cnt              ) as duty_cnt                 --七星潭判责总票数(未达成票数)
        ,sum(jingtai_my_dbc_cnt    ) as jingtai_my_dbc_cnt       --七星潭静态慢于（多班次）'
        ,sum(xiangzhen_js_cnt      ) as xiangzhen_js_cnt         --七星潭乡镇加时票数'
        ,sum(zhongzhuan_lsdd_cnt   ) as zhongzhuan_lsdd_cnt      --七星潭临时调度调度票数'
        ,sum(beiyongly_cnt         ) as beiyongly_cnt            --七星潭备用路由票数
        ,sum(zhuanyou_cnt          ) as zhuanyou_cnt             --七星潭转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_city_code = duty_city_code)
    and duty_type is not null
    group by order_source_code
            ,order_source_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_agent_code
            ,end_agent_name
            ,cainiao_plan_sign_date  --菜鸟平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_city_code     = reach.start_city_code
      and main.start_city_name     = reach.start_city_name
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.start_agent_code    = reach.start_agent_code
      and main.start_agent_name    = reach.start_agent_name
      and main.end_city_code       = reach.end_city_code
      and main.end_city_name       = reach.end_city_name
      and main.end_provider_code   = reach.end_provider_code
      and main.end_provider_name   = reach.end_provider_name
      and main.end_agent_code      = reach.end_agent_code
      and main.end_agent_name      = reach.end_agent_name
      and main.cainiao_plan_sign_date = reach.cainiao_plan_sign_date --菜鸟平台规划签收日期


union all
--代理区-代理区
select
     '代理区-代理区' as search_type
    ,'自有平台'      as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,null as start_provider_code
    ,null as start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,null as end_provider_code
    ,null as end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,null as jingtai_lyqs_cnt       --七星潭静态路由缺失票数
    ,null as dizhi_jxcw_cnt         --七星潭地址解析错误票数
    ,null as jingtai_my_cnt         --七星潭静态慢于票数
    ,null as jiedanhou_ls_cnt       --七星潭截单后揽收票数
    ,null as jiaojian_jbcw_cnt      --七星潭交件建包错误票数
    ,null as cuojiao_cnt            --七星潭错交票数
    ,null as jiaojian_bzd_cnt       --七星潭交件不准点票数
    ,null as zhongzhuan_jbcw_cnt    --七星潭中转建包错误票数
    ,null as cuofa_cnt              --七星潭错发票数
    ,null as fengche_wd_cnt         --七星潭封车晚点票数
    ,null as caozuo_yw_cnt          --七星潭操作延误票数
    ,null as fache_wd_cnt           --七星潭发车晚点票数
    ,null as yunshu_wd_cnt          --七星潭运输晚点票数
    ,null as jingang_zybjs_cnt      --七星潭进港转运不及时票数
    ,null as zhongzhuan_cf_cnt      --七星潭中转错分票数
    ,null as sanhuo_bjs_cnt         --七星潭散货不及时票数
    ,null as paijian_cf_cnt         --七星潭派件错分票数
    ,null as qianshou_wd_cnt        --七星潭签收晚点票数
    ,null as tuizhuan_cnt           --七星潭退转件票数
    ,null as duty_cnt               --七星潭判责总票数(未达成票数)
    ,main.zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,main.zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,main.zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,main.zy_cuojiao_cnt            --自有平台错交票数
    ,main.zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,main.zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,main.zy_cuofa_cnt              --自有平台错发票数
    ,main.zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,main.zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,main.zy_fache_wd_cnt           --自有平台发车晚点票数
    ,main.zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,main.zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,main.zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,main.zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,main.zy_paijian_cf_cnt         --自有平台派件错分票数
    ,main.zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,main.zy_tuizhuan_cnt           --自有平台退转件票数
    ,main.zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  --达成率总票数
    ,null as reach_cnt              --七星潭达成率达成总票数
    ,null as not_reach_cnt          --七星潭达成率未达成总票数
    ,reach.zy_reach_cnt             --达成率达成总票数
    ,reach.zy_not_reach_cnt         --达成率未达成总票数
    ,null as cainiao_plan_sign_date --菜鸟平台规划签收日期
    ,reach.ziyou_plan_sign_date     --自有平台规划签收日期
    ,null as jingtai_my_dbc_cnt     --七星潭静态慢于(多班次)票数
    ,null as xiangzhen_js_cnt       --七星潭乡镇加时票数
    ,null as zhongzhuan_lsdd_cnt    --七星潭临时调度票数
    ,main.zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,null as beiyongly_cnt          --七星潭备用路由票数
    ,null as zhuanyou_cnt           --七星潭转邮件票数
    ,main.zy_beiyongly_cnt          --自有平台备用路由票数
    ,main.zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.ziyou_plan_sign_date as dt
from (
    select
         start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(zy_reach_cnt     ) as  zy_reach_cnt      --达成率达成总票数
        ,sum(zy_not_reach_cnt ) as  zy_not_reach_cnt  --达成率未达成总票数
    from dm_prescription_reach_details_dt
    where ziyou_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期

) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(zy_dizhi_jxcw_cnt     ) as zy_dizhi_jxcw_cnt        --自有平台地址解析错误票数
        ,sum(zy_jiedanhou_ls_cnt   ) as zy_jiedanhou_ls_cnt      --自有平台截单后揽收票数
        ,sum(zy_jiaojian_jbcw_cnt  ) as zy_jiaojian_jbcw_cnt     --自有平台交件建包错误票数
        ,sum(zy_cuojiao_cnt        ) as zy_cuojiao_cnt           --自有平台错交票数
        ,sum(zy_jiaojian_bzd_cnt   ) as zy_jiaojian_bzd_cnt      --自有平台交件不准点票数
        ,sum(zy_zhongzhuan_jbcw_cnt) as zy_zhongzhuan_jbcw_cnt   --自有平台中转建包错误票数
        ,sum(zy_cuofa_cnt          ) as zy_cuofa_cnt             --自有平台错发票数
        ,sum(zy_fengche_wd_cnt     ) as zy_fengche_wd_cnt        --自有平台封车晚点票数
        ,sum(zy_caozuo_yw_cnt      ) as zy_caozuo_yw_cnt         --自有平台操作延误票数
        ,sum(zy_fache_wd_cnt       ) as zy_fache_wd_cnt          --自有平台发车晚点票数
        ,sum(zy_yunshu_wd_cnt      ) as zy_yunshu_wd_cnt         --自有平台运输晚点票数
        ,sum(zy_jingang_zybjs_cnt  ) as zy_jingang_zybjs_cnt     --自有平台进港转运不及时票数
        ,sum(zy_zhongzhuan_cf_cnt  ) as zy_zhongzhuan_cf_cnt     --自有平台中转错分票数
        ,sum(zy_sanhuo_bjs_cnt     ) as zy_sanhuo_bjs_cnt        --自有平台散货不及时票数
        ,sum(zy_paijian_cf_cnt     ) as zy_paijian_cf_cnt        --自有平台派件错分票数
        ,sum(zy_qianshou_wd_cnt    ) as zy_qianshou_wd_cnt       --自有平台签收晚点票数
        ,sum(zy_tuizhuan_cnt       ) as zy_tuizhuan_cnt          --自有平台退转件票数
        ,sum(zy_duty_cnt           ) as zy_duty_cnt              --自有平台判责总票数(未达成票数)
        ,sum(zy_zhongzhuan_lsdd_cnt) as zy_zhongzhuan_lsdd_cnt   --自有平台中转临时调度票数'
        ,sum(zy_beiyongly_cnt      ) as zy_beiyongly_cnt         --自有平台备用路由票数
        ,sum(zy_zhuanyou_cnt       ) as zy_zhuanyou_cnt          --自有平台转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_agent_code = ziyou_duty_main_agent_code)
    and ziyou_duty_type is not null
    and to_date(ziyou_plan_sign_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) main on main.order_source_code = reach.order_source_code
      and main.order_source_name = reach.order_source_name
      and main.start_agent_code  = reach.start_agent_code
      and main.start_agent_name  = reach.start_agent_name
      and main.end_agent_code    = reach.end_agent_code
      and main.end_agent_name    = reach.end_agent_name
      and nvl(main.ziyou_plan_sign_date,'999') = nvl(reach.ziyou_plan_sign_date,'999')   --自有平台规划签收日期

union all
--网点-网点
select
     '网点-网点' as search_type
    ,'自有平台'  as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,reach.start_network_code
    ,reach.start_network_name
    ,reach.start_city_code
    ,reach.start_city_name
    ,reach.start_provider_code
    ,reach.start_provider_name
    ,reach.start_fran_code
    ,reach.start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,reach.end_network_code
    ,reach.end_network_name
    ,reach.end_city_code
    ,reach.end_city_name
    ,reach.end_provider_code
    ,reach.end_provider_name
    ,reach.end_fran_code
    ,reach.end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,null as jingtai_lyqs_cnt       --七星潭静态路由缺失票数
    ,null as dizhi_jxcw_cnt         --七星潭地址解析错误票数
    ,null as jingtai_my_cnt         --七星潭静态慢于票数
    ,null as jiedanhou_ls_cnt       --七星潭截单后揽收票数
    ,null as jiaojian_jbcw_cnt      --七星潭交件建包错误票数
    ,null as cuojiao_cnt            --七星潭错交票数
    ,null as jiaojian_bzd_cnt       --七星潭交件不准点票数
    ,null as zhongzhuan_jbcw_cnt    --七星潭中转建包错误票数
    ,null as cuofa_cnt              --七星潭错发票数
    ,null as fengche_wd_cnt         --七星潭封车晚点票数
    ,null as caozuo_yw_cnt          --七星潭操作延误票数
    ,null as fache_wd_cnt           --七星潭发车晚点票数
    ,null as yunshu_wd_cnt          --七星潭运输晚点票数
    ,null as jingang_zybjs_cnt      --七星潭进港转运不及时票数
    ,null as zhongzhuan_cf_cnt      --七星潭中转错分票数
    ,null as sanhuo_bjs_cnt         --七星潭散货不及时票数
    ,null as paijian_cf_cnt         --七星潭派件错分票数
    ,null as qianshou_wd_cnt        --七星潭签收晚点票数
    ,null as tuizhuan_cnt           --七星潭退转件票数
    ,null as duty_cnt               --七星潭判责总票数(未达成票数)
    ,main.zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,main.zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,main.zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,main.zy_cuojiao_cnt            --自有平台错交票数
    ,main.zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,main.zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,main.zy_cuofa_cnt              --自有平台错发票数
    ,main.zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,main.zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,main.zy_fache_wd_cnt           --自有平台发车晚点票数
    ,main.zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,main.zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,main.zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,main.zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,main.zy_paijian_cf_cnt         --自有平台派件错分票数
    ,main.zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,main.zy_tuizhuan_cnt           --自有平台退转件票数
    ,main.zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  --达成率总票数
    ,null as reach_cnt              --七星潭达成率达成总票数
    ,null as not_reach_cnt          --七星潭达成率未达成总票数
    ,reach.zy_reach_cnt             --达成率达成总票数
    ,reach.zy_not_reach_cnt         --达成率未达成总票数
    ,null as cainiao_plan_sign_date --菜鸟平台规划签收日期
    ,reach.ziyou_plan_sign_date     --自有平台规划签收日期
    ,null as jingtai_my_dbc_cnt     --七星潭静态慢于(多班次)票数
    ,null as xiangzhen_js_cnt       --七星潭乡镇加时票数
    ,null as zhongzhuan_lsdd_cnt    --七星潭临时调度票数
    ,main.zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,null as beiyongly_cnt          --七星潭备用路由票数
    ,null as zhuanyou_cnt           --七星潭转邮件票数
    ,main.zy_beiyongly_cnt          --自有平台备用路由票数
    ,main.zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.ziyou_plan_sign_date as dt
from (
    select
         start_network_code
        ,start_network_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_fran_code
        ,start_fran_name
        ,start_agent_code
        ,start_agent_name
        ,end_network_code
        ,end_network_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_fran_code
        ,end_fran_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(zy_reach_cnt     ) as  zy_reach_cnt       --达成率达成总票数
        ,sum(zy_not_reach_cnt ) as  zy_not_reach_cnt   --达成率未达成总票数
    from dm_prescription_reach_details_dt
    where ziyou_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_network_code
            ,start_network_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_fran_code
            ,start_fran_name
            ,start_agent_code
            ,start_agent_name
            ,end_network_code
            ,end_network_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_fran_code
            ,end_fran_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_network_code
        ,start_network_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_fran_code
        ,start_fran_name
        ,start_agent_code
        ,start_agent_name
        ,end_network_code
        ,end_network_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_fran_code
        ,end_fran_name
        ,end_agent_code
        ,end_agent_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(zy_dizhi_jxcw_cnt     ) as zy_dizhi_jxcw_cnt        --自有平台地址解析错误票数
        ,sum(zy_jiedanhou_ls_cnt   ) as zy_jiedanhou_ls_cnt      --自有平台截单后揽收票数
        ,sum(zy_jiaojian_jbcw_cnt  ) as zy_jiaojian_jbcw_cnt     --自有平台交件建包错误票数
        ,sum(zy_cuojiao_cnt        ) as zy_cuojiao_cnt           --自有平台错交票数
        ,sum(zy_jiaojian_bzd_cnt   ) as zy_jiaojian_bzd_cnt      --自有平台交件不准点票数
        ,sum(zy_zhongzhuan_jbcw_cnt) as zy_zhongzhuan_jbcw_cnt   --自有平台中转建包错误票数
        ,sum(zy_cuofa_cnt          ) as zy_cuofa_cnt             --自有平台错发票数
        ,sum(zy_fengche_wd_cnt     ) as zy_fengche_wd_cnt        --自有平台封车晚点票数
        ,sum(zy_caozuo_yw_cnt      ) as zy_caozuo_yw_cnt         --自有平台操作延误票数
        ,sum(zy_fache_wd_cnt       ) as zy_fache_wd_cnt          --自有平台发车晚点票数
        ,sum(zy_yunshu_wd_cnt      ) as zy_yunshu_wd_cnt         --自有平台运输晚点票数
        ,sum(zy_jingang_zybjs_cnt  ) as zy_jingang_zybjs_cnt     --自有平台进港转运不及时票数
        ,sum(zy_zhongzhuan_cf_cnt  ) as zy_zhongzhuan_cf_cnt     --自有平台中转错分票数
        ,sum(zy_sanhuo_bjs_cnt     ) as zy_sanhuo_bjs_cnt        --自有平台散货不及时票数
        ,sum(zy_paijian_cf_cnt     ) as zy_paijian_cf_cnt        --自有平台派件错分票数
        ,sum(zy_qianshou_wd_cnt    ) as zy_qianshou_wd_cnt       --自有平台签收晚点票数
        ,sum(zy_tuizhuan_cnt       ) as zy_tuizhuan_cnt          --自有平台退转件票数
        ,sum(zy_duty_cnt           ) as zy_duty_cnt              --自有平台判责总票数(未达成票数)
        ,sum(zy_zhongzhuan_lsdd_cnt) as zy_zhongzhuan_lsdd_cnt   --自有平台中转临时调度票数'
        ,sum(zy_beiyongly_cnt      ) as zy_beiyongly_cnt         --自有平台备用路由票数
        ,sum(zy_zhuanyou_cnt       ) as zy_zhuanyou_cnt          --自有平台转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_network_code = ziyou_duty_main_code)
    and ziyou_duty_type is not null
    and to_date(ziyou_plan_sign_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by order_source_code
            ,order_source_name
            ,start_network_code
            ,start_network_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_fran_code
            ,start_fran_name
            ,start_agent_code
            ,start_agent_name
            ,end_network_code
            ,end_network_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_fran_code
            ,end_fran_name
            ,end_agent_code
            ,end_agent_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_network_code  = reach.start_network_code
      and main.start_network_name  = reach.start_network_name
      and main.start_city_code     = reach.start_city_code
      and main.start_city_name     = reach.start_city_name
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.start_fran_code     = reach.start_fran_code
      and main.start_fran_name     = reach.start_fran_name
      and main.start_agent_code    = reach.start_agent_code
      and main.start_agent_name    = reach.start_agent_name
      and main.end_network_code    = reach.end_network_code
      and main.end_network_name    = reach.end_network_name
      and main.end_city_code       = reach.end_city_code
      and main.end_city_name       = reach.end_city_name
      and main.end_provider_code   = reach.end_provider_code
      and main.end_provider_name   = reach.end_provider_name
      and main.end_fran_code       = reach.end_fran_code
      and main.end_fran_name       = reach.end_fran_name
      and main.end_agent_code      = reach.end_agent_code
      and main.end_agent_name      = reach.end_agent_name
      and nvl(main.ziyou_plan_sign_date,'999') = nvl(reach.ziyou_plan_sign_date,'999')   --自有平台规划签收日期



union all
--中心-中心
select
     '中心-中心' as search_type
    ,'自有平台'  as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,null as start_provider_code
    ,null as start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,reach.start_center_code
    ,reach.start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,null as end_provider_code
    ,null as end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,reach.end_center_code
    ,reach.end_center_name
    ,null as jingtai_lyqs_cnt       --七星潭静态路由缺失票数
    ,null as dizhi_jxcw_cnt         --七星潭地址解析错误票数
    ,null as jingtai_my_cnt         --七星潭静态慢于票数
    ,null as jiedanhou_ls_cnt       --七星潭截单后揽收票数
    ,null as jiaojian_jbcw_cnt      --七星潭交件建包错误票数
    ,null as cuojiao_cnt            --七星潭错交票数
    ,null as jiaojian_bzd_cnt       --七星潭交件不准点票数
    ,null as zhongzhuan_jbcw_cnt    --七星潭中转建包错误票数
    ,null as cuofa_cnt              --七星潭错发票数
    ,null as fengche_wd_cnt         --七星潭封车晚点票数
    ,null as caozuo_yw_cnt          --七星潭操作延误票数
    ,null as fache_wd_cnt           --七星潭发车晚点票数
    ,null as yunshu_wd_cnt          --七星潭运输晚点票数
    ,null as jingang_zybjs_cnt      --七星潭进港转运不及时票数
    ,null as zhongzhuan_cf_cnt      --七星潭中转错分票数
    ,null as sanhuo_bjs_cnt         --七星潭散货不及时票数
    ,null as paijian_cf_cnt         --七星潭派件错分票数
    ,null as qianshou_wd_cnt        --七星潭签收晚点票数
    ,null as tuizhuan_cnt           --七星潭退转件票数
    ,null as duty_cnt               --七星潭判责总票数(未达成票数)
    ,main.zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,main.zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,main.zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,main.zy_cuojiao_cnt            --自有平台错交票数
    ,main.zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,main.zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,main.zy_cuofa_cnt              --自有平台错发票数
    ,main.zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,main.zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,main.zy_fache_wd_cnt           --自有平台发车晚点票数
    ,main.zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,main.zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,main.zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,main.zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,main.zy_paijian_cf_cnt         --自有平台派件错分票数
    ,main.zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,main.zy_tuizhuan_cnt           --自有平台退转件票数
    ,main.zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  --达成率总票数
    ,null as reach_cnt              --七星潭达成率达成总票数
    ,null as not_reach_cnt          --七星潭达成率未达成总票数
    ,reach.zy_reach_cnt             --达成率达成总票数
    ,reach.zy_not_reach_cnt         --达成率未达成总票数
    ,null as cainiao_plan_sign_date --菜鸟平台规划签收日期
    ,reach.ziyou_plan_sign_date     --自有平台规划签收日期
    ,null as jingtai_my_dbc_cnt     --七星潭静态慢于(多班次)票数
    ,null as xiangzhen_js_cnt       --七星潭乡镇加时票数
    ,null as zhongzhuan_lsdd_cnt    --七星潭临时调度票数
    ,main.zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,null as beiyongly_cnt          --七星潭备用路由票数
    ,null as zhuanyou_cnt           --七星潭转邮件票数
    ,main.zy_beiyongly_cnt          --自有平台备用路由票数
    ,main.zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.ziyou_plan_sign_date as dt
from (
    select
         start_agent_code
        ,start_agent_name
        ,start_center_code
        ,start_center_name
        ,end_agent_code
        ,end_agent_name
        ,end_center_code
        ,end_center_name
        ,order_source_code
        ,order_source_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(zy_reach_cnt     ) as  zy_reach_cnt       --达成率达成总票数
        ,sum(zy_not_reach_cnt ) as  zy_not_reach_cnt   --达成率未达成总票数
    from dm_prescription_reach_details_dt
    where ziyou_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_agent_code
            ,start_agent_name
            ,start_center_code
            ,start_center_name
            ,end_agent_code
            ,end_agent_name
            ,end_center_code
            ,end_center_name
            ,order_source_code
            ,order_source_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,start_center_code
        ,start_center_name
        ,end_agent_code
        ,end_agent_name
        ,end_center_code
        ,end_center_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(zy_dizhi_jxcw_cnt     ) as zy_dizhi_jxcw_cnt        --自有平台地址解析错误票数
        ,sum(zy_jiedanhou_ls_cnt   ) as zy_jiedanhou_ls_cnt      --自有平台截单后揽收票数
        ,sum(zy_jiaojian_jbcw_cnt  ) as zy_jiaojian_jbcw_cnt     --自有平台交件建包错误票数
        ,sum(zy_cuojiao_cnt        ) as zy_cuojiao_cnt           --自有平台错交票数
        ,sum(zy_jiaojian_bzd_cnt   ) as zy_jiaojian_bzd_cnt      --自有平台交件不准点票数
        ,sum(zy_zhongzhuan_jbcw_cnt) as zy_zhongzhuan_jbcw_cnt   --自有平台中转建包错误票数
        ,sum(zy_cuofa_cnt          ) as zy_cuofa_cnt             --自有平台错发票数
        ,sum(zy_fengche_wd_cnt     ) as zy_fengche_wd_cnt        --自有平台封车晚点票数
        ,sum(zy_caozuo_yw_cnt      ) as zy_caozuo_yw_cnt         --自有平台操作延误票数
        ,sum(zy_fache_wd_cnt       ) as zy_fache_wd_cnt          --自有平台发车晚点票数
        ,sum(zy_yunshu_wd_cnt      ) as zy_yunshu_wd_cnt         --自有平台运输晚点票数
        ,sum(zy_jingang_zybjs_cnt  ) as zy_jingang_zybjs_cnt     --自有平台进港转运不及时票数
        ,sum(zy_zhongzhuan_cf_cnt  ) as zy_zhongzhuan_cf_cnt     --自有平台中转错分票数
        ,sum(zy_sanhuo_bjs_cnt     ) as zy_sanhuo_bjs_cnt        --自有平台散货不及时票数
        ,sum(zy_paijian_cf_cnt     ) as zy_paijian_cf_cnt        --自有平台派件错分票数
        ,sum(zy_qianshou_wd_cnt    ) as zy_qianshou_wd_cnt       --自有平台签收晚点票数
        ,sum(zy_tuizhuan_cnt       ) as zy_tuizhuan_cnt          --自有平台退转件票数
        ,sum(zy_duty_cnt           ) as zy_duty_cnt              --自有平台判责总票数(未达成票数)
        ,sum(zy_zhongzhuan_lsdd_cnt) as zy_zhongzhuan_lsdd_cnt   --自有平台中转临时调度票数'
        ,sum(zy_beiyongly_cnt      ) as zy_beiyongly_cnt         --自有平台备用路由票数
        ,sum(zy_zhuanyou_cnt       ) as zy_zhuanyou_cnt          --自有平台转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_center_code = ziyou_duty_main_code)
    and ziyou_duty_type is not null
    and to_date(ziyou_plan_sign_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,start_center_code
            ,start_center_name
            ,end_agent_code
            ,end_agent_name
            ,end_center_code
            ,end_center_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_agent_code    = reach.start_agent_code
      and main.start_agent_name    = reach.start_agent_name
      and main.start_center_code   = reach.start_center_code
      and main.start_center_name   = reach.start_center_name
      and main.end_agent_code      = reach.end_agent_code
      and main.end_agent_name      = reach.end_agent_name
      and main.end_center_code     = reach.end_center_code
      and main.end_center_name     = reach.end_center_name
      and nvl(main.ziyou_plan_sign_date,'999') = nvl(reach.ziyou_plan_sign_date,'999')   --自有平台规划签收日期


union all
--省份-省份
select
     '省份-省份' as search_type
    ,'自有平台'  as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,null as start_city_code
    ,null as start_city_name
    ,reach.start_provider_code
    ,reach.start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,null as end_city_code
    ,null as end_city_name
    ,reach.end_provider_code
    ,reach.end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,null as jingtai_lyqs_cnt       --七星潭静态路由缺失票数
    ,null as dizhi_jxcw_cnt         --七星潭地址解析错误票数
    ,null as jingtai_my_cnt         --七星潭静态慢于票数
    ,null as jiedanhou_ls_cnt       --七星潭截单后揽收票数
    ,null as jiaojian_jbcw_cnt      --七星潭交件建包错误票数
    ,null as cuojiao_cnt            --七星潭错交票数
    ,null as jiaojian_bzd_cnt       --七星潭交件不准点票数
    ,null as zhongzhuan_jbcw_cnt    --七星潭中转建包错误票数
    ,null as cuofa_cnt              --七星潭错发票数
    ,null as fengche_wd_cnt         --七星潭封车晚点票数
    ,null as caozuo_yw_cnt          --七星潭操作延误票数
    ,null as fache_wd_cnt           --七星潭发车晚点票数
    ,null as yunshu_wd_cnt          --七星潭运输晚点票数
    ,null as jingang_zybjs_cnt      --七星潭进港转运不及时票数
    ,null as zhongzhuan_cf_cnt      --七星潭中转错分票数
    ,null as sanhuo_bjs_cnt         --七星潭散货不及时票数
    ,null as paijian_cf_cnt         --七星潭派件错分票数
    ,null as qianshou_wd_cnt        --七星潭签收晚点票数
    ,null as tuizhuan_cnt           --七星潭退转件票数
    ,null as duty_cnt               --七星潭判责总票数(未达成票数)
    ,main.zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,main.zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,main.zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,main.zy_cuojiao_cnt            --自有平台错交票数
    ,main.zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,main.zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,main.zy_cuofa_cnt              --自有平台错发票数
    ,main.zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,main.zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,main.zy_fache_wd_cnt           --自有平台发车晚点票数
    ,main.zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,main.zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,main.zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,main.zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,main.zy_paijian_cf_cnt         --自有平台派件错分票数
    ,main.zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,main.zy_tuizhuan_cnt           --自有平台退转件票数
    ,main.zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  --达成率总票数
    ,null as reach_cnt              --七星潭达成率达成总票数
    ,null as not_reach_cnt          --七星潭达成率未达成总票数
    ,reach.zy_reach_cnt             --达成率达成总票数
    ,reach.zy_not_reach_cnt         --达成率未达成总票数
    ,null as cainiao_plan_sign_date --菜鸟平台规划签收日期
    ,reach.ziyou_plan_sign_date     --自有平台规划签收日期
    ,null as jingtai_my_dbc_cnt     --七星潭静态慢于(多班次)票数
    ,null as xiangzhen_js_cnt       --七星潭乡镇加时票数
    ,null as zhongzhuan_lsdd_cnt    --七星潭临时调度票数
    ,main.zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,null as beiyongly_cnt          --七星潭备用路由票数
    ,null as zhuanyou_cnt           --七星潭转邮件票数
    ,main.zy_beiyongly_cnt          --自有平台备用路由票数
    ,main.zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.ziyou_plan_sign_date as dt
from (
    select
         start_provider_code
        ,start_provider_name
        ,end_provider_code
        ,end_provider_name
        ,order_source_code
        ,order_source_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(zy_reach_cnt     ) as  zy_reach_cnt       --达成率达成总票数
        ,sum(zy_not_reach_cnt ) as  zy_not_reach_cnt   --达成率未达成总票数
    from dm_prescription_reach_details_dt
    where ziyou_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_provider_code
            ,start_provider_name
            ,end_provider_code
            ,end_provider_name
            ,order_source_code
            ,order_source_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_provider_code
        ,start_provider_name
        ,end_provider_code
        ,end_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_agent_code
        ,end_agent_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(zy_dizhi_jxcw_cnt     ) as zy_dizhi_jxcw_cnt        --自有平台地址解析错误票数
        ,sum(zy_jiedanhou_ls_cnt   ) as zy_jiedanhou_ls_cnt      --自有平台截单后揽收票数
        ,sum(zy_jiaojian_jbcw_cnt  ) as zy_jiaojian_jbcw_cnt     --自有平台交件建包错误票数
        ,sum(zy_cuojiao_cnt        ) as zy_cuojiao_cnt           --自有平台错交票数
        ,sum(zy_jiaojian_bzd_cnt   ) as zy_jiaojian_bzd_cnt      --自有平台交件不准点票数
        ,sum(zy_zhongzhuan_jbcw_cnt) as zy_zhongzhuan_jbcw_cnt   --自有平台中转建包错误票数
        ,sum(zy_cuofa_cnt          ) as zy_cuofa_cnt             --自有平台错发票数
        ,sum(zy_fengche_wd_cnt     ) as zy_fengche_wd_cnt        --自有平台封车晚点票数
        ,sum(zy_caozuo_yw_cnt      ) as zy_caozuo_yw_cnt         --自有平台操作延误票数
        ,sum(zy_fache_wd_cnt       ) as zy_fache_wd_cnt          --自有平台发车晚点票数
        ,sum(zy_yunshu_wd_cnt      ) as zy_yunshu_wd_cnt         --自有平台运输晚点票数
        ,sum(zy_jingang_zybjs_cnt  ) as zy_jingang_zybjs_cnt     --自有平台进港转运不及时票数
        ,sum(zy_zhongzhuan_cf_cnt  ) as zy_zhongzhuan_cf_cnt     --自有平台中转错分票数
        ,sum(zy_sanhuo_bjs_cnt     ) as zy_sanhuo_bjs_cnt        --自有平台散货不及时票数
        ,sum(zy_paijian_cf_cnt     ) as zy_paijian_cf_cnt        --自有平台派件错分票数
        ,sum(zy_qianshou_wd_cnt    ) as zy_qianshou_wd_cnt       --自有平台签收晚点票数
        ,sum(zy_tuizhuan_cnt       ) as zy_tuizhuan_cnt          --自有平台退转件票数
        ,sum(zy_duty_cnt           ) as zy_duty_cnt              --自有平台判责总票数(未达成票数)
        ,sum(zy_zhongzhuan_lsdd_cnt) as zy_zhongzhuan_lsdd_cnt   --自有平台中转临时调度票数'
        ,sum(zy_beiyongly_cnt      ) as zy_beiyongly_cnt         --自有平台备用路由票数
        ,sum(zy_zhuanyou_cnt       ) as zy_zhuanyou_cnt          --自有平台转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_provider_code = ziyou_duty_provider_code)
    and ziyou_duty_type is not null
    and to_date(ziyou_plan_sign_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by order_source_code
            ,order_source_name
            ,start_provider_code
            ,start_provider_name
            ,end_provider_code
            ,end_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_agent_code
            ,end_agent_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.end_provider_code   = reach.end_provider_code
      and main.end_provider_name   = reach.end_provider_name
      and main.start_agent_code = reach.start_agent_code
      and main.start_agent_name = reach.start_agent_name
      and main.end_agent_code   = reach.end_agent_code
      and main.end_agent_name   = reach.end_agent_name
      and nvl(main.ziyou_plan_sign_date,'999') = nvl(reach.ziyou_plan_sign_date,'999')   --自有平台规划签收日期

union all
--城市-城市
select
     '城市-城市' as search_type
    ,'自有平台'  as effect_type
    ,reach.order_source_code
    ,reach.order_source_name
    ,null as start_network_code
    ,null as start_network_name
    ,reach.start_city_code
    ,reach.start_city_name
    ,reach.start_provider_code
    ,reach.start_provider_name
    ,null as start_fran_code
    ,null as start_fran_name
    ,reach.start_agent_code
    ,reach.start_agent_name
    ,null as start_center_code
    ,null as start_center_name
    ,null as end_network_code
    ,null as end_network_name
    ,reach.end_city_code
    ,reach.end_city_name
    ,reach.end_provider_code
    ,reach.end_provider_name
    ,null as end_fran_code
    ,null as end_fran_name
    ,reach.end_agent_code
    ,reach.end_agent_name
    ,null as end_center_code
    ,null as end_center_name
    ,null as jingtai_lyqs_cnt       --七星潭静态路由缺失票数
    ,null as dizhi_jxcw_cnt         --七星潭地址解析错误票数
    ,null as jingtai_my_cnt         --七星潭静态慢于票数
    ,null as jiedanhou_ls_cnt       --七星潭截单后揽收票数
    ,null as jiaojian_jbcw_cnt      --七星潭交件建包错误票数
    ,null as cuojiao_cnt            --七星潭错交票数
    ,null as jiaojian_bzd_cnt       --七星潭交件不准点票数
    ,null as zhongzhuan_jbcw_cnt    --七星潭中转建包错误票数
    ,null as cuofa_cnt              --七星潭错发票数
    ,null as fengche_wd_cnt         --七星潭封车晚点票数
    ,null as caozuo_yw_cnt          --七星潭操作延误票数
    ,null as fache_wd_cnt           --七星潭发车晚点票数
    ,null as yunshu_wd_cnt          --七星潭运输晚点票数
    ,null as jingang_zybjs_cnt      --七星潭进港转运不及时票数
    ,null as zhongzhuan_cf_cnt      --七星潭中转错分票数
    ,null as sanhuo_bjs_cnt         --七星潭散货不及时票数
    ,null as paijian_cf_cnt         --七星潭派件错分票数
    ,null as qianshou_wd_cnt        --七星潭签收晚点票数
    ,null as tuizhuan_cnt           --七星潭退转件票数
    ,null as duty_cnt               --七星潭判责总票数(未达成票数)
    ,main.zy_dizhi_jxcw_cnt         --自有平台地址解析错误票数
    ,main.zy_jiedanhou_ls_cnt       --自有平台截单后揽收票数
    ,main.zy_jiaojian_jbcw_cnt      --自有平台交件建包错误票数
    ,main.zy_cuojiao_cnt            --自有平台错交票数
    ,main.zy_jiaojian_bzd_cnt       --自有平台交件不准点票数
    ,main.zy_zhongzhuan_jbcw_cnt    --自有平台中转建包错误票数
    ,main.zy_cuofa_cnt              --自有平台错发票数
    ,main.zy_fengche_wd_cnt         --自有平台封车晚点票数
    ,main.zy_caozuo_yw_cnt          --自有平台操作延误票数
    ,main.zy_fache_wd_cnt           --自有平台发车晚点票数
    ,main.zy_yunshu_wd_cnt          --自有平台运输晚点票数
    ,main.zy_jingang_zybjs_cnt      --自有平台进港转运不及时票数
    ,main.zy_zhongzhuan_cf_cnt      --自有平台中转错分票数
    ,main.zy_sanhuo_bjs_cnt         --自有平台散货不及时票数
    ,main.zy_paijian_cf_cnt         --自有平台派件错分票数
    ,main.zy_qianshou_wd_cnt        --自有平台签收晚点票数
    ,main.zy_tuizhuan_cnt           --自有平台退转件票数
    ,main.zy_duty_cnt               --自有平台判责总票数(未达成票数)
    ,reach.all_cnt                  --达成率总票数
    ,null as reach_cnt              --七星潭达成率达成总票数
    ,null as not_reach_cnt          --七星潭达成率未达成总票数
    ,reach.zy_reach_cnt             --达成率达成总票数
    ,reach.zy_not_reach_cnt         --达成率未达成总票数
    ,null as cainiao_plan_sign_date --菜鸟平台规划签收日期
    ,reach.ziyou_plan_sign_date     --自有平台规划签收日期
    ,null as jingtai_my_dbc_cnt     --七星潭静态慢于(多班次)票数
    ,null as xiangzhen_js_cnt       --七星潭乡镇加时票数
    ,null as zhongzhuan_lsdd_cnt    --七星潭临时调度票数
    ,main.zy_zhongzhuan_lsdd_cnt    --自有平台临时调度票数
    ,null as beiyongly_cnt          --七星潭备用路由票数
    ,null as zhuanyou_cnt           --七星潭转邮件票数
    ,main.zy_beiyongly_cnt          --自有平台备用路由票数
    ,main.zy_zhuanyou_cnt           --自有平台转邮件票数
    ,reach.ziyou_plan_sign_date as dt
from (
    select
         start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_agent_code
        ,end_agent_name
        ,order_source_code
        ,order_source_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(all_cnt          ) as  all_cnt            --达成率总票数
        ,sum(zy_reach_cnt     ) as  zy_reach_cnt       --达成率达成总票数
        ,sum(zy_not_reach_cnt ) as  zy_not_reach_cnt   --达成率未达成总票数
    from dm_prescription_reach_details_dt
    where ziyou_plan_sign_date between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_agent_code
            ,end_agent_name
            ,order_source_code
            ,order_source_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期
) reach
left join (
    select
         order_source_code
        ,order_source_name
        ,start_city_code
        ,start_city_name
        ,start_provider_code
        ,start_provider_name
        ,start_agent_code
        ,start_agent_name
        ,end_city_code
        ,end_city_name
        ,end_provider_code
        ,end_provider_name
        ,end_agent_code
        ,end_agent_name
        ,ziyou_plan_sign_date    --自有平台规划签收日期
        ,sum(zy_dizhi_jxcw_cnt     ) as zy_dizhi_jxcw_cnt        --自有平台地址解析错误票数
        ,sum(zy_jiedanhou_ls_cnt   ) as zy_jiedanhou_ls_cnt      --自有平台截单后揽收票数
        ,sum(zy_jiaojian_jbcw_cnt  ) as zy_jiaojian_jbcw_cnt     --自有平台交件建包错误票数
        ,sum(zy_cuojiao_cnt        ) as zy_cuojiao_cnt           --自有平台错交票数
        ,sum(zy_jiaojian_bzd_cnt   ) as zy_jiaojian_bzd_cnt      --自有平台交件不准点票数
        ,sum(zy_zhongzhuan_jbcw_cnt) as zy_zhongzhuan_jbcw_cnt   --自有平台中转建包错误票数
        ,sum(zy_cuofa_cnt          ) as zy_cuofa_cnt             --自有平台错发票数
        ,sum(zy_fengche_wd_cnt     ) as zy_fengche_wd_cnt        --自有平台封车晚点票数
        ,sum(zy_caozuo_yw_cnt      ) as zy_caozuo_yw_cnt         --自有平台操作延误票数
        ,sum(zy_fache_wd_cnt       ) as zy_fache_wd_cnt          --自有平台发车晚点票数
        ,sum(zy_yunshu_wd_cnt      ) as zy_yunshu_wd_cnt         --自有平台运输晚点票数
        ,sum(zy_jingang_zybjs_cnt  ) as zy_jingang_zybjs_cnt     --自有平台进港转运不及时票数
        ,sum(zy_zhongzhuan_cf_cnt  ) as zy_zhongzhuan_cf_cnt     --自有平台中转错分票数
        ,sum(zy_sanhuo_bjs_cnt     ) as zy_sanhuo_bjs_cnt        --自有平台散货不及时票数
        ,sum(zy_paijian_cf_cnt     ) as zy_paijian_cf_cnt        --自有平台派件错分票数
        ,sum(zy_qianshou_wd_cnt    ) as zy_qianshou_wd_cnt       --自有平台签收晚点票数
        ,sum(zy_tuizhuan_cnt       ) as zy_tuizhuan_cnt          --自有平台退转件票数
        ,sum(zy_duty_cnt           ) as zy_duty_cnt              --自有平台判责总票数(未达成票数)
        ,sum(zy_zhongzhuan_lsdd_cnt) as zy_zhongzhuan_lsdd_cnt   --自有平台中转临时调度票数'
        ,sum(zy_beiyongly_cnt      ) as zy_beiyongly_cnt         --自有平台备用路由票数
        ,sum(zy_zhuanyou_cnt       ) as zy_zhuanyou_cnt          --自有平台转邮件票数
    from jms_dm.dm_whole_decide_duty_main_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',15) and date_add('{{ execution_date | cst_ds }}',5)
    and (end_city_code = ziyou_duty_city_code)
    and ziyou_duty_type is not null
    and to_date(ziyou_plan_sign_date) between date_sub('{{ execution_date | cst_ds }}',10) and date_add('{{ execution_date | cst_ds }}',5)
    group by order_source_code
            ,order_source_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_agent_code
            ,start_agent_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_agent_code
            ,end_agent_name
            ,ziyou_plan_sign_date    --自有平台规划签收日期 
) main on main.order_source_code   = reach.order_source_code
      and main.order_source_name   = reach.order_source_name
      and main.start_city_code     = reach.start_city_code    
      and main.start_city_name     = reach.start_city_name    
      and main.start_provider_code = reach.start_provider_code
      and main.start_provider_name = reach.start_provider_name
      and main.start_agent_code    = reach.start_agent_code   
      and main.start_agent_name    = reach.start_agent_name   
      and main.end_city_code       = reach.end_city_code      
      and main.end_city_name       = reach.end_city_name      
      and main.end_provider_code   = reach.end_provider_code  
      and main.end_provider_name   = reach.end_provider_name  
      and main.end_agent_code      = reach.end_agent_code     
      and main.end_agent_name      = reach.end_agent_name 
      and nvl(main.ziyou_plan_sign_date,'999') = nvl(reach.ziyou_plan_sign_date,'999')   --自有平台规划签收日期
distribute by dt,pmod(hash(rand()),80)
;


